package com.sailfish.springbootdemo.dao.db7;

import com.sailfish.springbootdemo.common.DB7TableName;
import com.sailfish.springbootdemo.pojo.db7.TbExcipientsData;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;
import java.util.Map;

public interface TbExcipientsDataDao {

    @Results(id = "tbExcipientsDataResultMap", value = {
            @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER),
            @Result(column = "version", property = "version", jdbcType = JdbcType.VARCHAR),
            @Result(column = "template_type", property = "templateType", jdbcType = JdbcType.VARCHAR),
            @Result(column = "param_name", property = "paramName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "param_name_en", property = "paramNameEn", jdbcType = JdbcType.VARCHAR),
            @Result(column = "param_key", property = "paramKey", jdbcType = JdbcType.VARCHAR),
            @Result(column = "param_value", property = "paramValue", jdbcType = JdbcType.VARCHAR),
            @Result(column = "param_type", property = "paramType", jdbcType = JdbcType.INTEGER),
            @Result(column = "level", property = "level", jdbcType = JdbcType.INTEGER),
            @Result(column = "select_level", property = "selectLevel", jdbcType = JdbcType.INTEGER),
            @Result(column = "parent_select_id", property = "parentSelectId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "tip", property = "tip", jdbcType = JdbcType.VARCHAR),
            @Result(column = "remark", property = "remark", jdbcType = JdbcType.VARCHAR),
            @Result(column = "create_user", property = "createUser", jdbcType = JdbcType.VARCHAR),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_user", property = "updateUser", jdbcType = JdbcType.VARCHAR),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER),
            @Result(column = "row_sort", property = "rowSort", jdbcType = JdbcType.INTEGER),
            @Result(column = "sort", property = "sort", jdbcType = JdbcType.INTEGER),
            @Result(column = "is_value", property = "isValue", jdbcType = JdbcType.INTEGER)
    })
    // 查询方法
    @Select("<script>" +
            "SELECT * FROM " + DB7TableName.TB_EXCIPIENTS_DATA +
            " WHERE 1=1 " +
            "<if test='condition != null'> " +
            "    AND ( "  +
            "            (param_key = #{condition.key} AND param_value LIKE CONCAT('%', #{condition.value}, '%'))" +
            "    ) " +
            "</if>" +
            "</script>")
    List<TbExcipientsData> getValidByCondition(@Param("condition") Map<String, String> condition);

    // 插入方法
    @Insert("INSERT INTO " + DB7TableName.TB_EXCIPIENTS_DATA + "(version, template_type, param_name, param_name_en, param_key, param_value, param_type, level, select_level, parent_select_id, tip, remark, sort, create_user, create_time, row_sort) " +
            "VALUES(#{version}, #{templateType}, #{paramName}, #{paramNameEn}, #{paramKey}, #{paramValue}, #{paramType}, #{level}, #{selectLevel}, #{parentSelectId}, #{tip}, #{remark}, #{sort}, #{createUser}, NOW(), #{rowSort})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insert(TbExcipientsData tbExcipientsData);

    // 批量插入方法
    @Insert("<script> " +
            "INSERT INTO " + DB7TableName.TB_EXCIPIENTS_DATA + " (version, template_type, param_name, param_name_en, param_key, param_value, param_type, level, select_level, parent_select_id, tip, remark, sort, create_user, create_time, row_sort) " +
            "VALUES " +
            "<foreach collection='list' item='item' separator=','> " +
            "(#{item.version}, #{item.templateType}, #{item.paramName}, #{item.paramNameEn}, #{item.paramKey}, #{item.paramValue}, #{item.paramType}, #{item.level}, #{item.selectLevel}, #{item.parentSelectId}, #{item.tip}, #{item.remark}, #{item.sort}, #{item.createUser}, NOW(), #{item.rowSort}) " +
            "</foreach> " +
            "</script>")
    Integer batchInsert(@Param("list") List<TbExcipientsData> list);

    // 更新方法
    @Update("<script> " +
            "UPDATE " + DB7TableName.TB_EXCIPIENTS_DATA + " " +
            "<set> " +
            "<if test='tbExcipientsData.paramName != null'>param_name = #{tbExcipientsData.paramName},</if> " +
            "<if test='tbExcipientsData.paramValue != null'>param_value = #{tbExcipientsData.paramValue},</if> " +
            "<if test='tbExcipientsData.paramType != null'>param_type = #{tbExcipientsData.paramType},</if> " +
            "<if test='tbExcipientsData.level != null'>level = #{tbExcipientsData.level},</if> " +
            "<if test='tbExcipientsData.selectLevel != null'>select_level = #{tbExcipientsData.selectLevel},</if> " +
            "<if test='tbExcipientsData.parentSelectId != null'>parent_select_id = #{tbExcipientsData.parentSelectId},</if> " +
            "<if test='tbExcipientsData.tip != null'>tip = #{tbExcipientsData.tip},</if> " +
            "<if test='tbExcipientsData.remark != null'>remark = #{tbExcipientsData.remark},</if> " +
            "<if test='tbExcipientsData.sort != null'>sort = #{tbExcipientsData.sort},</if> " +
            "update_time = NOW()," +
            "<if test='tbExcipientsData.updateUser != null'>update_user = #{tbExcipientsData.updateUser},</if> " +
            // 如果 updateUser 可能为空，但你想用默认值，可改为 always set update_user
            "</set> " +
            "WHERE row_sort = #{tbExcipientsData.rowSort} " +
            "  AND param_key = #{tbExcipientsData.paramKey} " +
            "</script>")
    Integer updateByParamKey(@Param("tbExcipientsData") TbExcipientsData tbExcipientsData);

    // 批量更新方法
    @Update("<script> " +
            "UPDATE " + DB7TableName.TB_EXCIPIENTS_DATA +
            "<set> " +
            "<if test='list[0].version != null'>version=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.version != null'>WHEN id=#{item.id} THEN #{item.version}</if> " +
            "</foreach> " +
            "<if test='list[0].version != null'>END,</if> " +

            "<if test='list[0].templateType != null'>template_type=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.templateType != null'>WHEN id=#{item.id} THEN #{item.templateType}</if> " +
            "</foreach> " +
            "<if test='list[0].templateType != null'>END,</if> " +

            "<if test='list[0].paramName != null'>param_name=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.paramName != null'>WHEN id=#{item.id} THEN #{item.paramName}</if> " +
            "</foreach> " +
            "<if test='list[0].paramName != null'>END,</if> " +

            "<if test='list[0].paramNameEn != null'>param_name_en=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.paramNameEn != null'>WHEN id=#{item.id} THEN #{item.paramNameEn}</if> " +
            "</foreach> " +
            "<if test='list[0].paramNameEn != null'>END,</if> " +

            "<if test='list[0].paramKey != null'>param_key=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.paramKey != null'>WHEN id=#{item.id} THEN #{item.paramKey}</if> " +
            "</foreach> " +
            "<if test='list[0].paramKey != null'>END,</if> " +

            "<if test='list[0].paramValue != null'>param_value=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.paramValue != null'>WHEN id=#{item.id} THEN #{item.paramValue}</if> " +
            "</foreach> " +
            "<if test='list[0].paramValue != null'>END,</if> " +

            "<if test='list[0].paramType != null'>param_type=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.paramType != null'>WHEN id=#{item.id} THEN #{item.paramType}</if> " +
            "</foreach> " +
            "<if test='list[0].paramType != null'>END,</if> " +

            "<if test='list[0].level != null'>level=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.level != null'>WHEN id=#{item.id} THEN #{item.level}</if> " +
            "</foreach> " +
            "<if test='list[0].level != null'>END,</if> " +

            "<if test='list[0].selectLevel != null'>select_level=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.selectLevel != null'>WHEN id=#{item.id} THEN #{item.selectLevel}</if> " +
            "</foreach> " +
            "<if test='list[0].selectLevel != null'>END,</if> " +

            "<if test='list[0].parentSelectId != null'>parent_select_id=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.parentSelectId != null'>WHEN id=#{item.id} THEN #{item.parentSelectId}</if> " +
            "</foreach> " +
            "<if test='list[0].parentSelectId != null'>END,</if> " +

            "<if test='list[0].tip != null'>tip=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.tip != null'>WHEN id=#{item.id} THEN #{item.tip}</if> " +
            "</foreach> " +
            "<if test='list[0].tip != null'>END,</if> " +

            "<if test='list[0].remark != null'>remark=CASE</if> " +
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.remark != null'>WHEN id=#{item.id} THEN #{item.remark}</if> " +
            "</foreach> " +
            "<if test='list[0].remark != null'>END,</if> " +

            "<if test='list[0].rowSort != null'>row_sort=CASE</if> " + // 新增字段批量更新
            "<foreach collection='list' item='item' index='index'> " +
            "<if test='item.rowSort != null'>WHEN id=#{item.id} THEN #{item.rowSort}</if> " +
            "</foreach> " +
            "update_time=NOW() " +
            "</set> " +
            "WHERE id IN " +
            "<foreach collection='list' item='item' open='(' separator=',' close=')'> " +
            "#{item.id} " +
            "</foreach> " +
            "</script>")
    Integer batchUpdate(@Param("list") List<TbExcipientsData> dataList);

    // 删除方法
    @Delete("DELETE FROM " + DB7TableName.TB_EXCIPIENTS_DATA + " WHERE id=#{id}")
    Integer deleteById(@Param("id") Integer id);

    @Delete("DELETE FROM " + DB7TableName.TB_EXCIPIENTS_DATA + " WHERE row_sort=#{rowSort}")
    Integer deleteByRowSort(@Param("rowSort") Integer rowSort);

    @Select("<script>" +
            "select * from " + DB7TableName.TB_EXCIPIENTS_DATA +" where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "</script>")
    @ResultMap(value = "tbExcipientsDataResultMap")
    List<TbExcipientsData> getByVersion(@Param("templateType")String templateType);

    // 查询所有
    @Select("SELECT * FROM " + DB7TableName.TB_EXCIPIENTS_DATA)
    @ResultMap(value = "tbExcipientsDataResultMap")
    List<TbExcipientsData> getAll();

    @Select("SELECT id FROM " + DB7TableName.TB_EXCIPIENTS_DATA + " WHERE row_sort = #{rowSort}")
    List<Integer> getIdsByRowSort(@Param("rowSort")Integer rowSort);

    @Select("SELECT row_sort FROM " + DB7TableName.TB_EXCIPIENTS_DATA + " order by row_sort desc limit 1")
    Integer getMaxRowSort();
    @Select("SELECT sort FROM " + DB7TableName.TB_EXCIPIENTS_DATA + " order by sort desc limit 1")
    Integer getMaxSort();

    @Delete("DELETE FROM " + DB7TableName.TB_EXCIPIENTS_DATA)
    Integer deleteAll();

    @Delete("DELETE FROM " + DB7TableName.TB_EXCIPIENTS_DATA + " WHERE param_key=#{paramKey}")
    Integer deleteByParamKey(@Param("paramKey")String paramKey);
}
